Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / db3tips1.arc / DB3TAW.TXT < prev next >

Wrap

Text File | 1985-11-30 | 45.6 KB | 1,070 lines

dBASE III notes .001 Thomas A. Wells 146 D Street SE See BBS @ 321-7441 Washington, DC 20003 for other notes. (202)546-3409 I am providing a weekly series of these notes covering my experiences as a dBASEIII programmer. I work as a consultant and presently am spending 40+ hours per week writing programs and managing databases using primarily dBASEIII. This week's experience covers an odd occurrence. While interactively programming along, debugging a command file for displaying numbers of items, their record numbers and certain fields, the computer displayed the error message "File is already open". From my copy of the "PRODUCT USAGE REPORT" from A-T (see below), I found that this message should apply only if I tried to use a command file (.PRG) to call itself, which I wasn't! There followed an hour of trying to decide what was wrong, changing EXIT statement locations in various DO WHILE loops both in the main and sub-programs, and trying to figure out why the call had worked twice immediately before the error message appeared, etc. Finally it was time to call the A-T help line - (213)558-0086 (have your dBASE III serial number handy!). After some "Have you tried..." and "Yes, I have, but what about..." iterations, I was told to boot up the backup copy of the .EXE programs. Since I'm using a PC-XT, I put the disk in drive A, typed A:DBASE and lo! Everything worked! The final fix for the problem was to recopy the .EXE programs back to the hard disk from the original system disk with COPY *.EXE C:. Why did this work? I'm not sure, but it appears that the copy of the programs on the hard disk became corrupted, possibly in the stack management area. So far, after another week's run, no further problems have occurred. I'm not suggesting this as a fix for all programming problems, but if you're sure(?) of everything else, give it a try. The experience leaves me a little less sure of the integrity of either dBASEIII or IBM's hard disk. My backups have been more frequent lately. The PRODUCT USAGE REPORT mentioned above has been very helpful. I was sent it on request through the Technical Assistance phone number listed above. The last 10 pages or so list all the dBASEIII error messages and their meanings - very helpful to the programmer and, of course, left out of the original manual. The first half of the document consists of further tips, elucidations and comments regarding installation, programming usage of the various commands, and sections on the dCONVERT and dFORMAT utilities. I don't know whether the report is to be issued monthly or what - mine is dated October 1, 1984. If you're seriously programming with dBASEIII, get one! As a final thought for this week, using Wordstar with the program has been easy and helpful. Linking the two programs requires nothing more than creating a CONFIG.DB file with the statements WP=WS and TEDIT=WS in it. Be sure and run the Wordstar INSTALL program on the version you want to use before you do, so it will come up running in the non-document mode (menu E, letter C does the trick). - - - - - dBASE III notes .002 When adding records to a file, I sometimes like to stop and press F4, the function key that is programmed as "dir;". This gives a listing of the .DBF files only from within dBASEIII. The first time I did this, the file I just added a record to did not show the additional record! That is, the number of records was 89 prior to adding one and still showed 89 after the DIR command. The answer is relatively simple -- the directory does not appear to be updated as records are added -- a command to close the file seems to be required first, such as CLEAR ALL or CLOSE DATABASES. To see how many records have been added, issue the command CLEAR ALL (or equivalent), then DIR. Of course, there are many other ways to determine how many records a file has while in dBASEIII -- also true of nearly every way of doing almost anything. One could type GO BOTTOM, then ?RECNO(). Or if you had just added a record, ?RECNO() would work with out the GO BOTTOM. This week's puzzler happened while trying to index a .DBF file where the first index key was a string (character) variable, and the second key was a numeric variable. According to the rules, the numeric variable must be converted to a string via the STR() function, since multiple indexing keys must all be strings. So I did the conversion. Right in the INDEX command: INDEX ON string_var + STR(numeric_var) TO indexfile The result was puzzling, to say the least. Examination of the file using the EDIT command showed it to be in ascending order, as advertised, with respect to the string_var, but in descending order with respect to the numeric_var! Not what I wanted at all. The conversion to a string should not have affected order of the sort, since the numeric_var was less than 10 (single digit), and the string representation appeared to have the same number of leading blanks for each number. Also, the ASCII value of "2" is higher than that for "1", so the blanks should not have upset the order. The cure? Change the field definition to "character". Then, the indexing command becomes: INDEX ON string_var1 + string_var2 TO indexfile Examination of the ordering of the file then showed it to be organized in ascending order with respect to both keys. - - - - - dBASEIII notes .003 This week's dBASE III tip is for the CONFIG.DB file with thoughts about making screen displays easier to read and maybe a little fancier with bold, underline and graphics. CONFIG.DB... is a file that dBASEIII looks for after you type "DBASE". It may be used for many different purposes including tieing in a word processor other than the built-in one, setting most of the SET parameters and setting the function keys. Let's see how it is used to set the function keys. First, note that once again the manual (p. 1-126) is incorrect. There, we are told to include statements like: F2 = 'CLEAR;' to set the function keys. Not so! If you try this, an error message will greet you, and the key will not be set. The method used to set the function keys is: F2 = CLEAR; Notice that no quotation marks are used. Although it is not mentioned, there is a limit to the number of characters that a function key can represent. I don't know exactly what it is, but if you try to set one key to: F2 = SET PRINT OFF; SET DEVICE TO SCREEN; you will find that the letters at the end, say, "EEN;", have "wrapped" onto F3. To make the above workable, use the following statement: F2 = SET PRIN OFF;SET DEVI TO SCREE; Note the use of the 4-letter abbreviations for the commands, and that no blank is used after the first ";". Here's an example of a CONFIG.DB file using the above information: WP = WSN TEDIT = WSN F2 = clea all; F3 = modi comm F4 = set prin off;set devi to scre; As described in an earlier note, the first two statements link WORDSTAR (INSTALLed in the non-document mode) to the MEMO field editing and the MODIFY command editing. The last three statements set the function keys as described above. The only invisible trick is that for the F3 key there is a blank after the word "comm " so that all I need to do after pressing the F3 key is to type the name of the command file I wish to edit and press enter. The possibilities for using this feature are limited only by the programmer's imagination. If you use this feature for the final end- user program remember that you're really trying to make life simpler for them. GRAPHICS... are not too hard to do with dBASEIII. It will, however, take some patience and trial-and-error... My system is a PC-XT with the Hercules graphics board and the IBM mono monitor. I say this because the results of the statements outlined below will surely be different if you use other hardware. It all started with a desire to make a full screen look better, and also to make the important items stand out. First, DFORMAT was used to draw nice boxes on the screens where they would help make things clearer. Using DFORMAT, if anyone's interested, could be the subject of some entire later notes - it's truly an arcane challenge! After the boxes were drawn, and the .FMT file was generated, I naturally wanted to change something. Go back to DFORMAT? No way. It might be made to edit an existing file, but I'm over 40 and may not have enough time left to figure out how. So how to make changes to the graphics? All the ASCII equivalent characters from the BASIC manual - that's how! dBASEIII will happily put any character on the screen that is on that list, so there is a copy of it hanging on the wall next to my computer. To put a segment of a vertical double line on the screen, just enter: ? CHR(186) And there it is. Of course, @ SAY may also be used. BOLDFACE and UNDERLINE... are somewhat more involved, but easy once you get the idea. If the SET COLOR command is properly used for your hardware, all kinds of emphasis is possible. For you color monitor users, more experimenting will be needed. The challenge with a color monitor is not to overdo it. Here's the trick. For my hardware (above) typing: SET COLOR TO U1/0,0/7,0 results in everything being underlined. Note that the second parameter 0/7 leaves the @ SAY/GET reversed fields alone. Read the manual to see what is affected by each quantity between the parentheses - it's not ALWAYS wrong. Well there is one thing wrong - or at least it "works funny" for me. The underline is supposed to be turned on by the "U". On my computer, the "1" turns on the underline - the "U" can be omitted. To see how this can be put to use in a command file, here's one way to do it. Build a .MEM file by setting the values of "b", "bu", "u" and "off" as shown: Variable Value u "SET COLOR TO U1/0,0/7,0" off "SET COLOR TO 7/0,0/7,0" bu "SET COLOR TO +1/0,0/7,0" b "SET COLOR TO +7/0,0/7,0" After setting the values shown, type [SAVE TO color] [enter]. Then later in a command file use the RESTORE FROM color statement to activate the variables in memory. Don't forget the ADDITIVE modifier if other variables are active. "off" represents the default settings; I think they're same as when dBASEIII is first started. "u" produces underline, "bu" produces both boldface and underline, and "b" produces boldface - all on the screen of course. Then in a command file, or even from the dot prompt, type [&b] [enter] and everything from then 'til the [&off] occurs will be in boldface. The underline is slightly different. If you type [&u] [enter], every line on the screen will suddenly be underlined! Don't worry - if the command is issued in a printed line somewhere, it'll only underline where you tell it to. - - - - - dBASE III notes .004 December 19, 1984 This week - a newsflash, a correction and a print hint: NEWSFLASH Yesterday an update announcement from A-T arrived. The offer was to upgrade to dBASE III version 1.1! The details are that for $25 you get the "improved" version which will run on the PC-AT as well as the PC & XT, and can be "completely installed on any hard-disk IBM PC/XT, AT or 100% IBM PC compatible system". The brochure is understandably silent as to whether or not any of the bugs found by users and/or mentioned in their "Product Usage Re port" were fixed. My check is in the mail. My reaction to this is that since the program has been on the market less than 6 months, a charge for an update is an insult. Is this tactic of charging for each item separately an imitation of IBM? (If the bugs are reduced, maybe it'll be worth it. How about buyers who get version 1.0 this week? Will they get a "free" upgrade? If you know anyone about to invest in dBASE III, make sure they get the word. Surely all the present store stock hasn't been revised to version 1.1 yet.) CORRECTION Last note said there is no stated limit on the length of the SET FUNCTION command. My face is red - right there on page 4-113 of the manual it says 30 characters. PRINT HINT In formatting printouts to either/both the screen and printer it is useful to be able to embed a number inside text. For example, you may want to print: "There are 12 items in this list." In printing them or using @ SAY, the numbers will be preceded by up to 7 blank spaces. In the above example if we issued these commands: ? "There are" ?? n ?? "items in this list." The result would be: There are 12items in this list. Note that there are 6 blanks in front of the number and none after it. There doesn't appear to be any simple way to eliminate the extra blanks in front of the number. Adding one to the end is simple. If you know how long the number will be, say always 2 digits, the answer is simple. Just convert the number to a string and use the STR() function to print only the rightmost 2 characters. But what if the number of digits varies? Here is one way to handle that problem: ? "There are " ?? STR(n,(LOG(n+.5)/LOG(10)+1)) ?? " items in this list." Since there doesn't seem to be any easy way within dBASE III to tell how many digits a number has, the formula above takes your number, n, and uses the LOG() function to do the job, then the STR() function to print the exact number you wanted. The addition of the .5 to the number n is to overcome any internal inaccuracies in the LOG() function's number representation. The result then is: There are 12 items in this list. - - - - - dBASE III notes .005 January 15, 1984 This note deals with "!", "@" and "?". "!" is a very handy tool for the dBASE III programmer. It runs any system level program for you from within dBASE either from the dot prompt or from within a command file. It can be found in the manual under the equivalent syntax of "RUN". For example, I got tired of typing COPY *.dbf a:, etc. at the end of every session to back up all the files that may have changed, and besides that, the end user won't want to or maybe even won't know how to. So here's a backup routine that will back up all the files, with a built-in safety factor. If the operator puts in the wrong disk, an opportunity is given to swap it through the use of the FILE command. Here it is: *********************** BACKUP.PRG ************************** * Backs up .DBF, .MEM and .NDX files for the operator. * * An "invisible command" accessed by pressing "P" is * * included to back up .PRG and .FMT files. This program * * checks to see whether the proper disk is put in drive A * * and gives a chance to abort before the copying begins. * ************************************************************* CLEAR ALL CLEAR SET TALK OFF @ 10,10 SAY "Insert the backup disk into Drive A." @ 12,10 SAY 'Press "B" to begin, "A" to abort' WAIT SPACE(21) to j j = UPPER(j) * Turn the VERIFY function of DOS ON ! VERIFY ON DO CASE j CASE j = "B" * Check to see if correct backup disk is in drive A: IF FILE ("A:filename.DBF") SET CONSOLE OFF * Copy all the database & related files !COPY *.DBF A: !COPY *.MEM A: !COPY *.NDX A: SET CONSOLE ON ELSE * Argue if the disk isn't recognized... @ 12,10 CLEAR * Turn the bright letters on (see earlier note) &b @ 12,10 SAY 'This disk may not be the correct one!' &off @ 14,10 SAY 'When the correct one is inserted, press "B"' @ 16,10 SAY 'To Abort, press "A"' WAIT SPACE(31) TO b b = UPPER(b) DO CASE b CASE b = "B" SET CONSOLE OFF !COPY *.DBF A: !COPY *.MEM A: !COPY *.NDX A: SET CONSOLE ON CASE b = "A" RETURN ENDCASE b CASE j = "A" RETURN * This choice backs up the program and format files CASE j = "P" .AND. FILE('A:filename.PRG') !COPY *.PRG A: !COPY *.FMT A: ENDCASE j SET TALK ON SET CONSOLE ON !VERIFY OFF RETURN If your program files or database files are changed often, this program saves a lot of work. The other possibilities for using "!" (or "RUN", if you must) are intriguing. How about a communications program or another DOS function or ... ? "@" and "?" can both be used to print reports, among other things. "?" is preceded by a linefeed and a carriage return <CR>, while "??" is not. Together these two can be used to print many parts of any report, such as headings. Where formatted fields such as telephone numbers, dates or dollar amounts are to be printed, the PICTURE clause is handy, and "@" is probably easier to use. To have the output of both "?" and "@" routed to the printer, it is probably safer to use: SET PRINT ON SET DEVICE TO PRINT Then anything that either "@ SAY" or "?" does is done by the printer and not the console screen. "@ GET" is ignored. Watch out for putting the "@" commands into a sequence that causes the printer to back up either a line or space(s) on a line. You may get an unwanted EJECT by the printer. Here's an example of the use of a mixture of these commands: * Printer codes bold_on = CHR(27) + CHR(88) + CHR(49) bold_off = CHR(27) + CHR(88) + CHR(48) ? bold_on ?? heading ?? SPACE(20) + DTOC(DATE()) ? ? bold_off @ PROW(),0 SAY id_number PICTURE "@R 99-(999)-99" @ PROW(),PCOL()+2 SAY id_number_2 PICTURE "@R 99/999/A" @ PROW(),PCOL()+2 SAY SUBSTR(var1,1,14) @ PROW(),PCOL()+2 SAY var2 + ". " + var3 @ PROW(),PCOL()+2 SAY SUBSTR(var4,1,1) + ". " + var5 @ PROW(),PCOL() SAY RECNO() ? In the example the variables bold_on and bold_off are printer control codes covered in an earlier note, if memory serves. PROW() keeps the cursor on the same line. PCOL()+n spaces the data to match the heading. Either "?" or "@" could have been used to print var1 - var5 and RECNO(). The final "?" is to move the cursor to the beginning of the next line. Without it, any subsequent output will start at the end of RECNO(). Instead of PCOL() +n, a number may be used to select the starting column for the next item. @ PROW(),100 will place the first character in column 100 (just be sure your printer can accommodate column 100). Place your collection of "?" and "@" statements inside one or more DO WHILE loops and you're set to go. - - - - - dBASE III notes .006 January 24, 1984 Version 1.1 has arrived. Comments this time are about v1.1: Installation, Autostart, dFORMAT/SED, and "Oops". INSTALLATION Installation looks difficult, but is easy. The trick is to follow the directions. The bottom line is that yet another protection scheme is used, this time to allow hard disk users to boot directly from the hard disk without the system disk having to be in drive A:. To unravel the directions, you are apparently allowed one copy on the hard disk per system disk. The intent is that if you want to move the software to another machine you must "uninstall" the copy (put it back onto the system disk), then move the program to your other machine. Ridiculous. Even more ridiculous is A-T's statement that you may want to "uninstall" the program if "your hard disk becomes damaged"! They don't tell how to do this wonderful magic feat. I'm opposed to copy protection. There are already several small programs available for unprotecting dBASE III v1.0; those for v1.1 will be soon. AUTOSTART It is now practical (and legal) to boot your database from the hard disk using a combination of the AUTOEXEC.BAT and CONFIG. DB files. Here's how. The AUTOEXEC.BAT file has as its last two statements: CD\DBMS\DB3 dbase The first statement changes the default directory to the dBASE III directory and the last invokes the program. Since v1.1 requires no boot disk in drive A:, it comes alive and looks for a CONFIG.DB file. Mine ends with: COMMAND = DO mainmenu The result of these two additions to these files means that I can walk up to the computer, flip on the main switch and walk away. In about two minutes (using a 640K XT), the main menu is on the screen waiting for my use without ever pressing a key. See page 1-126 in the manual for a little more about CONFIG.DB and an example of COMMAND. dFORMAT/SED What was dFORMAT in v1.0 is SED in v1.1. The new version looks the same as the old, but it's a different size so maybe some of the bugs have been fixed. I liked dFORMAT so I plan to like SED. It is a good time-saver for creating screens for input and output. You can even use fancy boxes! Try it - the on-line help is good. OOPS After one day with v1.1, a bug appeared. Or maybe I should call it a correction to the bug. With v1.0, printing a date to the screen with @ SAY required a PICTURE clause like "@R 99/99/ 99" to get it to look like a date. On the printer, you didn't need it, which agreed with the manual (see page 4-20). That was one page that never made itself clear to me, except it encouraged me into some trial/error that resulted in my not using any PICTURE clauses with date variables except, as noted above, on the screen. If you happen to have a good explanation of page 4-20, please send it right away. Now v1.1 works the same as the printer. No PICTURE clauses needed on the screen. Is that clear from the documentation? No. Documentation for v1.1? Here's the total: Installation - 5 pages Corrections - 4 pages (scattered items) New pages - 1 SED comments - 4 It might be a good idea to funnel all the new and/or old bugs for dBASE III, particularly v1.1, to one person. The idea is to accumulate them and forward to users groups and A-T. - - - - - dBASE III notes .007 April 4, 1985 Today's a good day for some thoughts about screen and printer graphics. I use Wordstar for all my dBASE III programming. As a result, graphics are fairly simple to do, as I hope you'll see shortly. If you use Sidekick, or the dBASE III editor, or some other word processor the principles may or may not apply. The first problem is to get the graphics characters into the editor; that is, to make them available on the screen. I did it by using dFORMAT, supplied with v1.0. The "new" screen-making tool, SED, supplied with v1.1 will make you work harder for the same result. Or less. The process for getting control of lines and boxes is: 1. Draw a box any way you can in either SED or dFORMAT. (Or any other way - all's fair.) 2. Use SED or dFORMAT to generate the .PRG file. (Or see above parentheses) 3. Import the .PRG file into your word processor. 4. Use your word processor's functions for copying, moving and editing to construct other figures as needed. If you have dFORMAT, you can generate a box completely and import the whole thing into your word processor. If you're "stuck" with SED, it's a little more arcane. Here's a short list of keys and their effect on the screen for those with SED: Key CHR() Key CHR() F1 187 F2 200 F3 189 F4 190 F5 191 F6 192 And so on. Refer to your BASIC manual for the figures represented by the ASCII codes. With SED, if you use the SHIFT, ALT and CONTROL keys with the function keys (and even the backslash key) you can get some of the PC graphics & other special characters, but it's almost too time consuming. Besides, after all that I still wasn't able to make a complete box! The vertical line character managed to escape me completely. So I went back to dFORMAT, got both the single and double line samples, and started from there. As another possibility, I used Wordstar in the non-document mode to write this note, so the example below may be used, if you can ingest it with your dBASE III editor. Here's a sample box: @ 2,0 SAY "MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM" @ 2,55 SAY "MMMMMMMMMMMMMMMMMMMMMMM;" @ 3,0 SAY ":" @ 3,78 SAY ":" @ 4,0 SAY ":" @ 4,14 SAY "Whatever the TITLE of this here screen is..." @ 4,78 SAY ":" @ 5,0 SAY ":" @ 5,78 SAY ":" @ 6,0 SAY ":" @ 6,78 SAY ":" @ 7,0 SAY ":" @ 7,20 SAY "und so weiter..." @ 7,78 SAY ":" @ 8,0 SAY ':' @ 8,78 SAY ":" @ 9,0 SAY ":" @ 9,55 SAY ':' @ 10,0 SAY ":" @ 10,55 SAY ":" @ 11,0 SAY ":" @ 11,55 SAY ":" @ 12,0 SAY ":" @ 12,55 SAY ":" @ 13,0 SAY ":" @ 13,78 SAY ":" @ 14,0 SAY ":" @ 14,55 SAY ":" @ 15,0 SAY ":" @ 15,78 SAY ":" @ 16,0 SAY ":" @ 16,78 SAY ":" @ 17,0 SAY ":" @ 17,78 SAY ":" @ 18,0 SAY ":" @ 18,78 SAY ":" @ 19,0 SAY ":" @ 19,78 SAY ":" @ 20,0 SAY ":" @ 20,78 SAY ":" @ 21,0 SAY ":" @ 21,78 SAY ":" @ 22,0 SAY ":" @ 22,78 SAY ":" @ 23,0 SAY "MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM" @ 23,55 SAY "MMMMMMMMMMMMMMMMMMMMMMM<" The above code should, if you insert it in a .PRG or a .FMT file, produce a complete box on your screen or printer consisting of gen - yoo - wine PC graphics characters. If you drag this code (or the whole note) into your word processor, you may see only colons (:), H's (H), M's (M), semicolons (;), I's (I) and a less than sign (<). Don't be fooled; this is the way the graphics are displayed by Wordstar. Don't erase one of the "graphics" colons and then try to replace it with a colon from your keyboard - life don't work that way. The ASCII codes for the "real" colon and the "graphics" colon are quite different. By about 128... If you're still here, and have got this figure or an equivalent one into your word processor, you're ready for step 4. Move, copy and delete until you (think you) have reformed the size and shape of your box to suit your need. Then run (DO) the program. Chase the bugs until you get it right, and spruce up all your screens. As a side note, you can also "DO" a .FMT file! Also note that if you have a PC "compatible" printer, both single and double line graphics will print, though most printers print either as only a single line. - - - - - dBASE III notes .008 April 11, 1985 PEEK... is not documented, but a dBASE programmer can have some fun with it and perhaps learn a little about programming and screen handling in the process. So purists are excused from reading the following example program: ******** PEEK.PRG * Prints the location, value & CHR() where possible * OK up to location 32767 - modify for higher locations CLEAR INPUT "begin at ?" to begin INPUT "how many ?" to num loc = begin r = 4 c = 0 DO WHILE loc < begin + num * trim location number & put in proper location on screen * see earlier note for trim technique involving logs @ r,c*25 SAY STR(loc,(LOG(loc+.5)/LOG(10)+1)) pk = PEEK(loc) * take care of "unprintable" characters IF pk > 31 .AND. pk <255 @ ROW(),COL() SAY " CHR("+STR(pk,3)+ ") = "+CHR(pk) ELSE IF pk <> 0 @ ROW(),COL() SAY " CHR("+STR(pk,3) + ")" ELSE @ ROW(),COL() SAY " CHR( 0)" ENDIF ENDIF loc = loc + 1 * print downward on screen r = r + 1 * don't go off the bottom of the screen IF r > 22 r = 4 c = c + 1 * don't go off the right of the screen IF c > 2 c = 0 WAIT @ 4,0 CLEAR ENDIF ENDIF ENDDO * don't overwrite data on the screen when finished @ 23,0 SAY "" If you trim the above program out of this note and run it, you will see: - a request for the locations you want printed on the screen - a request for the number of locations after the start above - columns of data printed The programming techniques used are: - printing in columns because they're easier to read than rows - showing the ASCII representation where possible - managing to stay on the screen, avoiding errors There are two challenges here I'd like to offer. First, see if you can make the program work above location 32767. Second, with the start given, write a disassembler! At least for the memory locations below 32768; you could use a .dbf to store the op-codes and the associated decimal & hex values. Silly? Maybe not, if you consider it a way to learn. INDEXING... can be very helpful for quick access to data in any order, and is necessary for certain other commands such as SEEK, FIND, SET INDEX and SET RELATION. Once a file is INDEXed, it is important to back up the .ndx file whenever the .dbf file is backed up. If you don't back it up, loss of a disk will mean reconstructing the .ndx file. If you know what the index key fields were either from experience or documentation then rebuilding the index file is only time consuming. If you don't know what the key fields were, your problem could be quite substantial. One way to avoid the problem is to keep track of the .ndx file keys. Here's one way to do it: Back up your .ndx files along with the associated .dbf files. A suggested "easy" way to do this is contained in an earlier note. Then keep printed copies of your file structures handy by issuing the command "LIST STRUCTURE TO PRINT" every time you modify the file's structure. Then after each time you create an index file for the .dbf file, type "USE filename INDEX indexname" then press F6 (DISPLAY STATUS) to show the file in use. Note that the .ndx file's name and key fields are also shown. Do a screen print or "DISPLAY STATUS TO PRINT" and tape the index file info to the printout of the .dbf file structure. This process is actually quite easy. Remember to back up any related .mem and .dbt files along with the above. To quote Macpherson's Law: "Murphy's Law is too optimistic.". - - - - - dBASE III notes .009 April 30, 1985 This week's note concerns the "&" and the "*" & is the dBASE MACRO FUNCTION. It looks like an ampersand (because it is!) and works in ways that new dBASE programmers sometimes have a lot of trouble figuring out. First try the program below. It's short but it illustrates the use of the "&" and also the asterisk or NOTE function: **************** ASTERISK.prg * demonstrates the "&" and the "*" **************** a = "*" ? "a" + "&a" &a ? "This is line 1" a = "" &a ? "This is line 2" If the keyword (NOTE) or the asterisk symbol appears in the first column of a line, dBASE ignores the rest of the line. Same as the REM keyword or the apostrophe does in many versions of BASIC. When you run ASTERISK.prg this is what takes place: 1 - the variable "a" is set equal to an asterisk. 2 - the first print statement prints an "a" and then "the contents of a". If you think of the "&" symbol as "the contents of" it may help you see what is taking place. Therefore, since "the contents of a" is an asterisk, that's what appears on the screen. 3 - in the next line the first expression is &a. Since the & is the "contents of " function, the computer looks for its argument "a", sees it is an asterisk and puts that character first on the line. Seeing an asterisk first on a line causes the computer to see the line as a comment and ignore it! 4 - "a" is "nulled out", that is, "a" is set equal to null - the empty string. 5 - nearly the same line as before except that "a" is no longer an asterisk but a null, so the "?" or print statement is executed. The program's output? Here it is: do asterisk a* This is line 2 . Try the program with other values for a such as *fGHY67 and 1234. Expect and interpret different results and error messages. When you are comfortable with &, use it in other dBASE statements such as: SET FILTER TO fieldname = "&variable" LOCATE FOR fieldname = "&variable" and all those other places that "the contents of" a variable are needed. See notes .003 and .005 for examples of other uses of the & function. - - - - - dBASE III notes .010 May 21, 1985 This week's note concerns DO, IF, SUM and "&" macro substitution. It started innocently with a program as outlined below containing a DO WHILE and an IF statement, like this: * FOO.prg * or how to find a bug in dBASE III v1.1 * Initialize some variables date = CTOD("01/01/01") * these are all date field names f1 = "FIELD1" f2 = "B->FIELD2" . . . f28 = "FIELD28" * open the files SELECT 1 USE FILENO1 SELECT 2 USE FILENO2 * link the two files by record no. since there are many fields SELECT 1 SET RELATION TO RECNO() INTO FILENO2 * cause the problems GO TOP DO WHILE .NOT. EOF() IF &f1>date .AND..NOT.(&f23<date.OR.&f24<date.OR.; ........OR.&f28<date) SUM fieldname TO total FOR &f1>date .AND..NOT.(&f20<date.OR.; .........&f19<date.OR.&f18<date) ENDIF ENDDO ? "Title of the sum = " ?? total . . . etc Anyway, I hope you get the idea: an IF statment inside a DO loop, each with a looooong qualifying clause of the Boolean persuasion. When I tried to run the program, it executed fine until it hit the first record to fail the IF test. Then the dot prompt appeared without any error message and without executing the print statement following the DO WHILE! It was as though the CANCEL command had been executed, but at least with CANCEL you get a message like "DO cancelled". So the debugging began. First, there is a limit on line length. 254 characters. It was possible that the expansions caused by the macros might have created a line longer than 254 characters (true for most command lines in dBASE). So, shorten the line. Same problem. Less than 254 characters for sure. Now what? Second, they admitted that use of a PUBLIC variable in a SUM statement can cause incorrect summing. So I eliminated all PUBLIC variables in the program (Well, there were a few things I didn't tell you, but they didn't make any difference). No improvement. Then I copy the .prg file to FOO.prg and start eliminating statements and pieces of statements, a debugging technique I use when everything else has failed. The first thing out was one of the macro substitutions in the SUM statement. The program ran perfectly. Flushed with success, I made the same change to the original program. It failed. I went back to foo, put the macro back into the SUM statement and it ran perfectly! Checked the file for corrupted data. Everything fine. It still bothered me that with TALK ON, the IF statement was the last line executed before the message-free exit from the program occurred. I reexamined the program logic and decided I could do the job perfectly well without the IF or DO statements by suitably modifying the SUM statement. All the macro substitutions went into a large SUM statement. I got the answers about the data I needed for the report and had no more unexpected halts occur in the program. About all I can conclude from this exercise is that there IS a bug which allows dBASE to exit a loop - back to the dot prompt - without any hint as to why. I also confess to ignorance. Anyway, if you happen across anything like this, call or write. As to another similar occurrence with v1.0, that is, mysterious and inconclusive, see one of my earlier notes. - - - - - dBASE III notes .011 May 23, 1985 This week's note concerns INDEX, $ and debugging. INDEX... is the slickest way to make a file appear as though it was sorted even if it was not. Using: INDEX ON fieldname list TO indexfilename is a neat, fast way to organize data items to reduce print time or the time to recall or add data to a file. One of the problems appears in the latter area. Let's use a fictitious example program to illustrate. First suppose you have created the file FOO.dbf containing the imaginative fieldnames of FIELD1, FIELD2, ... FIELD30. Then you have entered data and now issue the command: INDEX ON FIELD1 + FIELD3 + FIELD22 TO FOO This statement creates the index file known as FOO.ndx which, when in use, makes the file appear sorted on the key FIELD1 and then the sub- keys FIELD3 and FIELD22. For example, if FIELD1 is the State and FIELD3 is the City, then the data will appear in State order and within each group of States it will appear sorted on the City, etc. From here on whenever you USE FOO, it will appear in the order you entered the data. If you USE FOO INDEX FOO, when you EDIT, SKIP or otherwise step through the file the data will appear as though it was sorted on the three "key fields" above: FIELD1, FIELD3 and FIELD22. How do you keep the index file updated and if you add data to any of the KEY fields (1, 3 or 22 above), when will the index file be updated? As usual in programming, there isn't one simple answer. Here are some ways - maybe you can think of others: 1. USE FOO ...add/edit data... SET INDEX TO FOO REINDEX 2. USE FOO INDEX FOO add data.. (reindexing automatically done) In short, if you update while the index is not in use, you will need to REINDEX afterward, while using the index file. If the index file is in use, it is automatically updated. So far so good. How about creating another index file for FOO.dbf? Let's say you do this: USE FOO INDEX ON FIELD17 + FIELD2 TO FOO2 Now you've got two index files for the same data file - FOO.ndx and FOO2.ndx. How can these both be updated quickly? If you try it, REINDEX can be slow, especially if the number of key fields is large. A quicker way is to use one of the SET commands: USE FOO SET INDEX TO FOO2, FOO This statement makes the file appear as though it is sorted on the keys in FOO2. FOO is along for the ride in that regard (read: doesn't do anything but is in use), but it is now also updated automatically whenever any of its key fields are changed. No reindexing needed. One caveat: be sure to pay attention to the manual's remarks about mass updates to key fields. If you do it wrong, you can lose large chunks of data! Read the section in the manual about SET while you're at it. There are quite a few handy items there, not only for indexing but also for relating one file to another either by record number or key field, setting printer margin, paths and so on... $... is the substring search function. The only reason I mention it here is due to an old habit of naming string or dollar amount variables with the "$" character somewhere in the name. Such as graph$, amount$ or similar. Well this old habit started when I was learning BASIC and dies hard. Interestingly, filenames like fn$ and the like work fine. So do the variable names like amount$. When I tried to get a little fancier and wrote "app$tot" as a variable name, I got caught. I've forgotten the specific error message, but you'll recognize it if you see it. In short watch out for using special characters in your variable names. If you use "&" in one, you won't get any sympathy from me at all. DEBUGGING... is an art as well as a science. It is estimated to take about 20% or more of the time involved in writing a well-planned program where the planning takes 50% of the time. For those who like to "sit down at the piano and play" with only an overall idea of the objectives of the software, debugging percentages can approach 100%. Some useful techniques to use are TALK, STEP, PUBLIC, CANCEL, DEBUG and ECHO. Here is a short look at how each might be used: SET TALK ON is probably the most generally useful technique. When issued as a command in the .prg file (or from the dot prompt if the .prg file doesn't SET TALK OFF and cancel that command), you can see the result of each line of the program printed on the screen (or the printer if you have SET PRINT ON). If a value of a variable is changed, it will be printed. When the error you are looking for occurs, you can tell which line is being executed (usually) and what the values of the most recently set variables are. Often, the value causing the problem may be set in another line somewhere earlier. PUBLIC... can be used to examine the variable values if the error you are chasing causes the program to stop and the dot prompt to appear, or if you press Escape. If you then press F7 (display memory), and none of the variables have been declared public, no values will appear. So to use PUBLIC, put the statement at the beginning of your command file like this: PUBLIC variable list and then F7 will give you their latest values. One caution about this technique: if your bug is in a SUM statement, find another way to debug! As noted in the previous note (#10), PUBLIC and SUM may not get along too well together. STEP... will allow the program to be executed one line at a time. If you need to stop the program after each line: SET STEP ON will do it. You'll be asked to press a key after each line of the program has executed. Useful for errors that can't be pinpointed to a particular line if the program isn't printing or if it's going too fast through a segment of the code for you to stop it easily. Issue the command either from within a program if you don't want the thing to ask you to press enter for every step before a certain point, or from the dot prompt if you do. CANCEL... will stop the program and put you back to the dot prompt from any place within a command file. It acts like a breakpoint in an assembly language program by letting you choose where to stop execution. If you use PUBLIC in the same program, the values of the variables will then be available at that line for you to examine. ECHO... is a little like TALK, except where TALK displays the values of any variables that change, ECHO displays (or prints) the command(s) that are executed. DEBUG... is handy for directing the output of the ECHO command to the printer instead of the screen if you want to keep the screen from being cluttered or overwritten while you are debugging. All of the above SET commands are described in the manual, sometimes even in more detail. The above hints are really only tools for debugging provided in the dBASE III programming language. The most important one is located between your ears. In the above debugging comments, various ways of employing these tools were mentioned. Now let's take a look at a few circumstances where you might want them. After you have planned and written a program, the next step will be to execute it. Print reports, add data, enter data or make calculations, for example. This is the sequence: 1. Plan the program logic 2. Break the logic into steps of smaller size. 3. Do step 2 until each step is (almost) a small enough segment to be coded directly from the logic. 4. Write the code. 5. Execute the code. 6. Debug the code. Steps 1-3 usually take 50% of the programming time. Step 4 can then be done in about 10% of the time. Step 6 consists of several discrete kinds of operations: 1. Syntax error checking. 2. Running test data. 3. Logic error checking. SYNTAX ERRORS... are usually the simplest kind of errors to correct. In most cases, the program will halt and an error message is printed. For most errors in dBASE III, a question mark will appear just to the right of the actual error. Then you correct the error and run again. Until there aren't any more syntax error messages. You might think that is that. Well, as you have by now guessed, there's a lot more. There are some errors that could be considered either syntax or logic errors that won't give you any message. For example, if you open file FOO in area 1 and GOO in area 2 and then try to GET FIELD2 from area 1 while you are in area 2. You forgot that you were in the wrong area and should have written GET A->FIELD2. In this case, the screen position where the result of the GET should have displayed will be blank. It is easy to think "there wasn't anything in the field in that record, so the blank is OK". The blank is NOT OK - it's an error. Thus you want to develop the habit of (at least mentally) questioning every item that is printed by your code. A blank may or may not really be a blank... RUN TEST DATA... The next important step in debugging is to run some test data. Make sure you know in advance what the output will be. Try to run data at both ends and the middle of your data range. For example, if your program accepts data between $40 and $4 million for a variable value, use both of these and several intermediate ones even if the usage of the extremes is rare. Then check the results by hand. LOGIC ERRORS... may be much easier to spot if you run test data. Wrong answers may show them (or may show errors in your hand calculations) much more efficiently than having your boss question them later at an inopportune moment. Other logic errors may show up in printing. Usually obviously. The sneakier kind are those that may show up in selection logic, for example: COUNT FOR YEAR(FIELD3)>0.AND.FIELD16<56.OR.FIELD2<variable TO a Here the Boolean logic should be assembled correctly so that "what you THOUGHT you told the computer to do IS ACTUALLY what you told the computer to do". Parentheses would make the above COUNT statement clearer or change its meaning entirely.